clear all
global data "~\CBO\CBO_DATA\"

import excel "${data}households_ranked_by_inc_before_trans_tax_average_household_income_2000_2006.xlsx", sheet("Tabelle2") firstrow


//recode the variables and keep only the important ones
keep if inrange(year,2000,2006)


gen y_gross=inc_before_transfers_taxes 
gen y_net=inc_after_transfers_taxes


//subtract medicaid like HSV
replace y_net=y_net-medicare

//gen quantile
encode income_group, gen(qtile)
//drop highest quintile
drop if qtile==2

//code weights
gen w=.
replace w=0.2 if qtile==3
replace w=0.2 if qtile==8
replace w=0.2 if qtile==4
replace w=0.2 if qtile==1
replace w=0.1 if qtile==5
replace w=0.05 if qtile==6
replace w=0.04 if qtile==7
replace w=0.01 if qtile==9

//keep relevant vars
keep qtile w y_gross y_net year


gen ly_gross=log(y_gross)
gen ly_net=log(y_net)

save ${data}CBOprep.dta, replace

